import csv
import os
import pymysql
import requests
import time

def str_to_float(str):
    if str == "--" or str == '':
        str = 0
    else:
        if "," in str:
            str = str.replace(",", "")
        str = float(str)
    return str


def curl_data(url):
    header = {
        "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh,zh-CN;q=0.9,zh-TW;q=0.8",
        "Cache-Control": "no-cache",
        "Connection": "keep-alive",
        "DNT": "1",
        "Host": "quotes.money.163.com",
        "Pragma": "no-cache",
        "Referer": url,
        "Upgrade-Insecure-Requests": "1",
        "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.74 Safari/537.36"
    }
    html_str = ""
    status_code = 999
    for i in range(10):
        html_a = requests.get(url, headers=header)
        if html_a.status_code == 200:
            html_bytes = html_a.content
            html_str = html_bytes.decode()
            status_code = html_a.status_code
            break
        else:
            print("【" + str(status_code) + "】" + url, "重试中：", status_code)
            time.sleep(1)

    print("【" + str(status_code) + "】" + url)
    return status_code, html_str


# 下载csv文件
def down_csv(url, name):
    path = "./temp/" + name + ".csv"
    status_code = 999
    for i in range(10):
        r = requests.get(url, allow_redirects=True)
        status_code = r.status_code
        if status_code == 200:
            with open(path, "wb") as f:
                f.write(r.content)
                f.close()
            break
    else:
        print("【" + str(status_code) + "】" + url, "重试中：", status_code)
        time.sleep(1)

    print("【" + str(status_code) + "】" + url)
    return path, status_code


# 打开csv文件并返回数据
def open_csv(path, encoding='GBK', is_del=0):
    with open(path, "r", encoding=encoding) as c_f:
        csv_rows_obj = csv.reader(c_f)
        csv_rows = []
        # 转换为列表
        for row in csv_rows_obj:
            del row[0]
            csv_rows.append(row)
        close_csv(c_f, is_del)
    return csv_rows


# 关闭csv句柄 is_del不删 0 删除 1
def close_csv(f, is_del=0):
    if is_del == 1:
        os.remove(f.name)
    f.close()


# 判断字符串是不是数字
def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass
    return False


# 根据符号计算
def math_ting(n_1, n_2, s):
    if s == "+":
        return n_1 + n_2
    elif s == "-":
        return n_1 - n_2
    elif s == "/":
        return n_1 / n_2
    elif s == "*":
        return n_1 * n_2


# 连接数据库
def connect_mysql(host, port, user, passwd, db_name):
    conn = pymysql.connect(host=host, port=port, user=user, password=passwd, database=db_name)
    return conn


# 写入数据库
def write_mysql(code, data, conn):
    cursor = conn.cursor()
    sql = "INSERT INTO `stock`.`financial_data` \
            ( `year`, `shares_number`, `advance_charge`, `receivable`, `gross_revenue`, `gm`," \
          " `operating_profit_margin`, `profit_margin`, `net_profit`, `free_cash_flow`, `roe`) VALUES " \
          "( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

    for y_key in data:
        params = (
            y_key,
            code,
            data[y_key]["advance_charge"],
            data[y_key]["receivable"],
            data[y_key]["gross_revenue"],
            data[y_key]["gm"],
            data[y_key]["operating_profit_margin"],
            data[y_key]["profit_margin"],
            data[y_key]["net_profit"],
            data[y_key]["free_cash_flow"],
            data[y_key]["roe"],
        )
        try:
            # 执行sql语句
            cursor.execute(sql, params)
            # 提交到数据库执行
            conn.commit()
        except EOFError as e:
            # 发生错误时回滚
            conn.rollback()
            print("数据写入失败", e)
